Creating a database using PHP

    Creating Database

    PHP allows the use of mysql_query function to create a database. But in order to create and delete a database, you must have admin rights on the database. This function takes two parameters to create a database.

    Syntax 

    bool mysql_query (sql, connection);
Where,

    SQL is the query with which you will create the database. Connection (optional) will be used to create the connection, if not mentioned then the last opened connection will be used.

    Example 

    <?php
$db_host = 'localhost:3036';
$db_user = 'root';
$db_pass = 'rootpassword';
$db_conn = mysql_connect($db_host, $db_user, $db_pass);
if(! $db_conn ) {
die('Error: ' . mysql_error());
}
echo 'Success';
$db_sql = 'CREATE Database demo_db';
$db_retval = mysql_query( $sql, $conn );
if(! $db_retval ) 
{
die('Database creation failed: ' . mysql_error());
}
echo "Database demo_db created\n";
mysql_close($db_conn);
?>

    Select the target database

    Once you are done creating the database and before creating the data table for that database, you need to select which database you are going to use. There can be many databases from which you can select the target one. PHP supports the use of mysql_select_db function to choose the database according to your requirement. Syntax 

    bool mysql_select_db (db_name, connection);

    Where, Db_name suggests which database you are going to use before performing any action. Connection (optional) will be used to create the connection, if not mentioned then the last opened connection will be used.

    Example 

    <?php
$db_host = 'localhost:3036';
$db_user = 'guest';
$db_pass = 'guest123';
$db_conn = mysql_connect($db_host, $db_user, $db_pass);
if(! $db_conn ) 
{
die('Connection failed: ' . mysql_error());
}
echo ‘Success';
mysql_select_db( 'demo_db' );
mysql_close($db_conn);
?>

    Creating tables within the database

    You can create the database tables by using the mysql_query function bypassing the table creation command in the SQL parameter. There are two methods with which you can create tables within the database. If you create table code is small then you can mention in the same file within the PHP code, but if your code is long then you can create a new file with table code and that file can be read from the PHP code in another file.

    Example 1 

    <?php
$db_host = 'localhost:3036';
$db_user = 'root';
$db_pass = 'rootpassword';
$db_conn = mysql_connect($db_host, $db_user, $db_pass);
if(! $db_conn ) {
die('Connection failed: ' . mysql_error());
}
echo 'Success';
$db_sql = 'CREATE TABLE student( '.
'stu_id INT NOT NULL AUTO_INCREMENT, '.
'stu_name VARCHAR(20) NOT NULL, '.
'stu_address VARCHAR(20) NOT NULL, '.
'primary key ( stu_id ))';
mysql_select_db('demo_db');
$db_retval = mysql_query( $db_sql, $conn );
if(! $db_retval ) {
die('Table creation failed: ' . mysql_error());
}
echo "Table student created successfully\n";
mysql_close($db_conn);
?>

    Example 2

    • Create the code in one file named demo.txt. 
    CREATE TABLE student(
stu_id INT NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(20) NOT NULL,
stu_address VARCHAR(20) NOT NULL,
primary key ( stu_id ));
    • Create a php code. 
    <?php
$db_host = 'localhost:3036';
$db_user = 'root';
$db_pass = 'rootpassword';
$db_conn = mysql_connect($db_host, $db_user, $db_pass);
if(! $db_conn ) {
die('Connection failed: ' . mysql_error());
}
$table_file = 'demo.txt';
$db_fp = fopen($table_file, 'r');
$db_sql = fread($db_fp, filesize($table_file));
fclose($db_fp);
mysql_select_db('demo_db');
$db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
die('Table creation failed: ' . mysql_error());
}
echo "Table student created successfully\n";
mysql_close($db_conn);
?>

    People are also reading:

    Previous Tutorial Next Tutorial